package com.example.repository;

import com.example.model.Payment;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 支付Repository
 */
@Repository
public interface PaymentRepository extends JpaRepository<Payment, Long> {

    /**
     * 根据支付单号查询
     */
    Optional<Payment> findByPaymentNo(String paymentNo);

    /**
     * 根据第三方支付单号查询
     */
    Optional<Payment> findByThirdPartyNo(String thirdPartyNo);

    /**
     * 根据订单ID查询支付记录
     */
    List<Payment> findByOrderIdOrderByCreatedAtDesc(Long orderId);

    /**
     * 根据订单ID和状态查询
     */
    Optional<Payment> findByOrderIdAndStatus(Long orderId, Payment.PaymentStatus status);

    /**
     * 根据用户ID查询支付记录
     */
    Page<Payment> findByUserIdOrderByCreatedAtDesc(String userId, Pageable pageable);

    /**
     * 根据商家ID查询支付记录
     */
    Page<Payment> findByMerchantIdOrderByCreatedAtDesc(Long merchantId, Pageable pageable);

    /**
     * 根据支付方式查询
     */
    List<Payment> findByPaymentMethodAndStatus(Payment.PaymentMethod paymentMethod, Payment.PaymentStatus status);

    /**
     * 查询过期的待支付订单
     */
    @Query("SELECT p FROM Payment p WHERE p.status = 'PENDING' AND p.expireTime < :now")
    List<Payment> findExpiredPendingPayments(@Param("now") LocalDateTime now);

    /**
     * 查询指定时间范围内的支付记录
     */
    @Query("SELECT p FROM Payment p WHERE p.createdAt BETWEEN :startTime AND :endTime")
    List<Payment> findByCreatedAtBetween(@Param("startTime") LocalDateTime startTime, 
                                        @Param("endTime") LocalDateTime endTime);

    /**
     * 统计用户支付总金额
     */
    @Query("SELECT COALESCE(SUM(p.actualAmount), 0) FROM Payment p WHERE p.userId = :userId AND p.status = 'SUCCESS'")
    BigDecimal sumSuccessAmountByUserId(@Param("userId") String userId);

    /**
     * 统计商家收入总金额
     */
    @Query("SELECT COALESCE(SUM(p.actualAmount), 0) FROM Payment p WHERE p.merchantId = :merchantId AND p.status = 'SUCCESS'")
    BigDecimal sumSuccessAmountByMerchantId(@Param("merchantId") Long merchantId);

    /**
     * 统计指定时间范围内的支付金额
     */
    @Query("SELECT COALESCE(SUM(p.actualAmount), 0) FROM Payment p WHERE p.status = 'SUCCESS' " +
           "AND p.paidAt BETWEEN :startTime AND :endTime")
    BigDecimal sumSuccessAmountBetween(@Param("startTime") LocalDateTime startTime, 
                                      @Param("endTime") LocalDateTime endTime);

    /**
     * 统计支付方式使用情况
     */
    @Query("SELECT p.paymentMethod, COUNT(p) FROM Payment p WHERE p.status = 'SUCCESS' " +
           "AND p.paidAt BETWEEN :startTime AND :endTime GROUP BY p.paymentMethod")
    List<Object[]> countByPaymentMethodBetween(@Param("startTime") LocalDateTime startTime, 
                                              @Param("endTime") LocalDateTime endTime);

    /**
     * 查询待处理的支付回调
     */
    @Query("SELECT p FROM Payment p WHERE p.status = 'PROCESSING' AND p.createdAt < :timeout")
    List<Payment> findTimeoutProcessingPayments(@Param("timeout") LocalDateTime timeout);

    /**
     * 查询用户今日支付次数
     */
    @Query("SELECT COUNT(p) FROM Payment p WHERE p.userId = :userId " +
           "AND p.createdAt >= :startOfDay AND p.createdAt < :endOfDay")
    Long countUserPaymentsToday(@Param("userId") String userId, 
                               @Param("startOfDay") LocalDateTime startOfDay,
                               @Param("endOfDay") LocalDateTime endOfDay);

    /**
     * 查询用户今日支付金额
     */
    @Query("SELECT COALESCE(SUM(p.actualAmount), 0) FROM Payment p WHERE p.userId = :userId " +
           "AND p.status = 'SUCCESS' AND p.paidAt >= :startOfDay AND p.paidAt < :endOfDay")
    BigDecimal sumUserPaymentAmountToday(@Param("userId") String userId,
                                        @Param("startOfDay") LocalDateTime startOfDay,
                                        @Param("endOfDay") LocalDateTime endOfDay);

    /**
     * 查询可退款的支付记录
     */
    @Query("SELECT p FROM Payment p WHERE p.status = 'SUCCESS' " +
           "AND (p.refundStatus = 'NO_REFUND' OR p.refundStatus = 'PARTIAL_REFUND') " +
           "AND p.actualAmount > p.refundAmount")
    List<Payment> findRefundablePayments();

    /**
     * 根据IP地址查询支付记录
     */
    List<Payment> findByClientIpAndCreatedAtBetween(String clientIp, LocalDateTime startTime, LocalDateTime endTime);

    /**
     * 查询异常支付记录
     */
    @Query("SELECT p FROM Payment p WHERE " +
           "(p.status = 'FAILED' OR p.status = 'CANCELLED') " +
           "AND p.createdAt BETWEEN :startTime AND :endTime")
    List<Payment> findAbnormalPayments(@Param("startTime") LocalDateTime startTime,
                                      @Param("endTime") LocalDateTime endTime);

    /**
     * 查询高频支付用户
     */
    @Query("SELECT p.userId, COUNT(p) as paymentCount FROM Payment p " +
           "WHERE p.createdAt >= :startTime " +
           "GROUP BY p.userId " +
           "HAVING COUNT(p) > :threshold " +
           "ORDER BY COUNT(p) DESC")
    List<Object[]> findHighFrequencyPaymentUsers(@Param("startTime") LocalDateTime startTime,
                                                 @Param("threshold") long threshold);

    /**
     * 查询大额支付记录
     */
    @Query("SELECT p FROM Payment p WHERE p.actualAmount >= :amount " +
           "AND p.status = 'SUCCESS' AND p.paidAt BETWEEN :startTime AND :endTime " +
           "ORDER BY p.actualAmount DESC")
    List<Payment> findLargeAmountPayments(@Param("amount") BigDecimal amount,
                                         @Param("startTime") LocalDateTime startTime,
                                         @Param("endTime") LocalDateTime endTime);

    /**
     * 统计支付成功率
     */
    @Query("SELECT " +
           "COUNT(CASE WHEN p.status = 'SUCCESS' THEN 1 END) as successCount, " +
           "COUNT(p) as totalCount " +
           "FROM Payment p WHERE p.createdAt BETWEEN :startTime AND :endTime")
    Object[] getPaymentSuccessRate(@Param("startTime") LocalDateTime startTime,
                                  @Param("endTime") LocalDateTime endTime);

    /**
     * 查询平均支付金额
     */
    @Query("SELECT AVG(p.actualAmount) FROM Payment p WHERE p.status = 'SUCCESS' " +
           "AND p.paidAt BETWEEN :startTime AND :endTime")
    BigDecimal getAveragePaymentAmount(@Param("startTime") LocalDateTime startTime,
                                      @Param("endTime") LocalDateTime endTime);

    /**
     * 查询支付渠道统计
     */
    @Query("SELECT p.paymentChannel, COUNT(p), SUM(p.actualAmount) FROM Payment p " +
           "WHERE p.status = 'SUCCESS' AND p.paidAt BETWEEN :startTime AND :endTime " +
           "GROUP BY p.paymentChannel ORDER BY COUNT(p) DESC")
    List<Object[]> getPaymentChannelStatistics(@Param("startTime") LocalDateTime startTime,
                                              @Param("endTime") LocalDateTime endTime);

    /**
     * 检查是否存在重复支付
     */
    @Query("SELECT COUNT(p) FROM Payment p WHERE p.orderId = :orderId " +
           "AND p.status IN ('SUCCESS', 'PROCESSING') AND p.id != :excludeId")
    Long countDuplicatePayments(@Param("orderId") Long orderId, @Param("excludeId") Long excludeId);

    /**
     * 查询需要对账的支付记录
     */
    @Query("SELECT p FROM Payment p WHERE p.status = 'SUCCESS' " +
           "AND p.paidAt BETWEEN :startTime AND :endTime " +
           "AND p.thirdPartyNo IS NOT NULL " +
           "ORDER BY p.paidAt")
    List<Payment> findPaymentsForReconciliation(@Param("startTime") LocalDateTime startTime,
                                               @Param("endTime") LocalDateTime endTime);
}
